﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace cau2
{
    class sinhvienDAL
    {
         public sinhvienDAL() { }
        public DataTable hienthi()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = "Data Source=(local); Initial Catalog=qlsv; User ID=sa; Password=sa";   
            conn.Open();
            SqlCommand cmd = new SqlCommand("select * from sinhvien", conn);
            SqlDataReader dr = cmd.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Load(dr);
            conn.Close();
            return dt;
        }
        public void Them(String ma, String ho, String ten, String day, String gioitinh, String makhoa)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = "Data Source=(local); Initial Catalog=qlsv; User ID=sa; Password=sa";
            conn.Open();
            SqlCommand cmd = new SqlCommand("insert into sinhvien values('"+ma+"',N'"+ho+"',N'"+ten+"','"+day+"',N'"+gioitinh+"','"+makhoa+"')", conn);
            cmd.ExecuteNonQuery();
            conn.Close();
        }
        public void Xoa(String ma)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = "Data Source=(local); Initial Catalog=qlsv; User ID=sa; Password=sa";
            conn.Open();
            SqlCommand cmd = new SqlCommand("delete from sinhvien where masv='" + ma + "'", conn);
            cmd.ExecuteNonQuery();
            conn.Close();
        }
        public void Sua(String ma, String ho, String ten, String day, String gioitinh, String makhoa)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = "Data Source=(local); Initial Catalog=qlsv; User ID=sa; Password=sa";
            conn.Open();
            SqlCommand cmd = new SqlCommand("update sinhvien set hosv=N'" + ho + "',tensv=N'" + ten + "',ngaysinh='" + day + "',gioitinh=N'" + gioitinh + "',makhoa='" + makhoa + "' where masv='" + ma + "'", conn);
            cmd.ExecuteNonQuery();
            conn.Close();
        }
    }
}
